package cn.su.dao.impl;

import cn.su.dao.providerDao;
import cn.su.pojo.provider;
import cn.su.utils.BaseDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class providerDaoimpl implements providerDao {

    @Override
    public List<provider> provider_List() {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="select * from smbms_provider";
        List<provider> providerList=null;
        try {
            providerList = queryRunner.query(sql, new BeanListHandler<>(provider.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return providerList;
    }

    @Override
    public List<provider>  provider_search(String queryProCode, String queryProName) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        StringBuffer sql=new StringBuffer("select * from smbms_provider where 1=1");
        if (queryProCode!=null&&!queryProCode.trim().equals("")){
            sql.append(" and proCode like '%"+queryProCode+"%'");
        }
        if (queryProName!=null&&!queryProName.trim().equals("")){
            sql.append(" and  proName like '%"+queryProName+"%'");
        }
        List<provider>  query = null;
        try {
            //用StringBuffer，要转成String
            query = queryRunner.query(sql.toString(), new BeanListHandler<>(provider.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }

    @Override
    public int provider_add(provider provider) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="insert into smbms_provider(proCode,proName,proContact,proPhone,proAddress,proFax,proDesc,creationDate) values(?,?,?,?,?,?,?,?)";
        int update=0;
        try {
             update = queryRunner.update(sql, provider.getProCode(), provider.getProName(), provider.getProContact(), provider.getProPhone(), provider.getProAddress(), provider.getProFax(), provider.getProDesc(),provider.getCreationDate());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return update;
    }
    public provider provider_chakan(int id){//根据id查看供货商
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String  sql="select * from smbms_provider where id = "+id;
        provider query=null;
        try {
             query = queryRunner.query(sql, new BeanHandler<>(provider.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }
    public  int provider_update(int id,provider provider){//根据id修改供货商
        QueryRunner queryRunner = BaseDao.getQueryRunner();
//        String sql="update smbms_provider set proName='"+provider.getProName()+"',proContact='"+provider.getProContact()+"',proPhone='"+provider.getProPhone()+"',proAddress='"+provider.getProAddress()+"',proFax='"+provider.getProFax()+"',proDesc='"+provider.getProDesc()+"',modifyDate='"+provider.getModifyDate()+"'  where id="+id;
        String sql="update smbms_provider set proName=?,proContact=?,proPhone=?,proAddress=?,proFax=?,proDesc=?,modifyDate=?  where id="+id;
        int i=0;
        try {
           i= queryRunner.update(sql,provider.getProName(),provider.getProContact(),provider.getProPhone(),provider.getProAddress(),provider.getProFax(),provider.getProDesc(),provider.getModifyDate());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }
    public  int provider_delete(int id){
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="delete from smbms_provider where id="+id;
        int i=0;
        try {
           i= queryRunner.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;

    }

    public static void main(String[] args) {
        providerDao dao=new providerDaoimpl();

        List<provider> list=new ArrayList<>();

        list=dao. provider_List();
        System.out.println(list);
    }
}
